﻿/*-------------------------------------------------------------------------
* 命名空间: WaferSelectV3.Common/ExcelHelper
* 类       名: ExcelHelper
* 功      能:  操作Excel表格的工具类
* 时      间:  2024-05-07 15:57:21
* 版      本:  V1.0
* 作      者:  Fioman
* 格      言:  Talk is cheap,show me the code ^_^
*-------------------------------------------------------------------------*/
using log4net.Core;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using WaferSelectV3.Extensions;
using WaferSelectV3.Models;

namespace WaferSelectV3.Common
{
    public class ExcelHelper
    {

        /// <summary>
        /// 将xlsx文件转换为 list<list<string>> 格式的数据,可以指定起始行,-1表示从第一行开始,行数,-1表示从起始行到结束的所有函数
        /// </summary>
        /// <param name="excelPath">excel文件全路径</param>
        /// <param name="sheetIndex">指定的工作簿的索引,默认是第一个工作簿,索引为0</param>
        /// <param name="rowStart">起始行</param>
        /// <param name="rowCount">行个数</param>
        /// <param name="colStart">起始列</param>
        /// <param name="colCount">列个数</param>
        /// <param name="hasTitle">是否含有标题</param>
        /// <returns></returns>
        public static DataResult<List<List<string>>> ExcelToListStringByFilePath(string excelPath, int sheetIndex = 0, int rowStart = -1,
            int rowCount = -1, int colStart = -1, int colCount = -1, bool hasTitle = true)
        {
            File.SetAttributes(excelPath, FileAttributes.ReadOnly);
            DataResult<List<List<string>>> result = new DataResult<List<List<string>>>();
            List<List<string>> resList = new List<List<string>>();
            try
            {
                // 设置EPPlus库的许可上下文为非商业用途
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                var package = new ExcelPackage(new FileInfo(excelPath));
                ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetIndex];
                rowStart = rowStart == -1 ? 1 : rowStart;
                rowCount = rowCount == -1 ? worksheet.Dimension.End.Row - rowStart + 1 : rowCount; // 一共多少行
                colStart = colStart == -1 ? 1 : colStart;
                colCount = colCount == -1 ? worksheet.Dimension.End.Column - colStart + 1 : colCount; // 一共多少列


                for (int i = rowStart; i <= rowCount; i++)
                {
                    List<string> rowData = new List<string>();
                    if (i == 1 && hasTitle)
                    {
                        continue;
                    }
                    for (int j = colStart; j <= colCount; j++)
                    {
                        var cellVal = worksheet.Cells[i, j].Value;
                        string? temp = cellVal == null ? string.Empty : cellVal.ToString();
                        rowData.Add(temp!);
                    }
                    resList.Add(rowData);
                }
                package.Dispose();
                result.Data = resList;
                result.State = true;
            }
            catch (Exception ex)
            {
                result.Message = ex.GetFullException();
            }

            return result;
        }


        /// <summary>
        /// 将xlsx文件转换为DataTable格式的数据,可以指定起始行,-1表示从第一行开始,行数,-1表示从起始行到结束的所有函数
        /// </summary>
        /// <param name="excelPath">excel文件全路径</param>
        /// <param name="sheetIndex">指定的工作簿的索引,默认是第一个工作簿,索引为0</param>
        /// <param name="rowStart">起始行</param>
        /// <param name="rowCount">行个数</param>
        /// <param name="colStart">起始列</param>
        /// <param name="colCount">列个数</param>
        /// <param name="hasTitle">是否含有标题</param>
        /// <returns></returns>
        public static DataResult<DataTable> ExcelToDataTableByFilePath(string excelPath, int sheetIndex = 0, int rowStart = -1,
            int rowCount = -1, int colStart = -1, int colCount = -1, bool hasTitle = true)
        {
            File.SetAttributes(excelPath, FileAttributes.ReadOnly);
            DataResult<DataTable> result = new DataResult<DataTable>();
            DataTable dt = new DataTable();
            DataRow row;
            try
            {
                // 设置EPPlus库的许可上下文为非商业用途
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                var package = new ExcelPackage(new FileInfo(excelPath));
                ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetIndex];
                rowStart = rowStart == -1 ? 1 : rowStart;
                rowCount = rowCount == -1 ? worksheet.Dimension.End.Row - rowStart + 1 : rowCount; // 一共多少行
                colStart = colStart == -1 ? 1 : colStart;
                colCount = colCount == -1 ? worksheet.Dimension.End.Column - colStart + 1 : colCount; // 一共多少列

                //如果没有标题就自动填充列名,这个列明代表字段名是必须要有的
                if (!hasTitle)
                {
                    for (int i = 0; i < colCount; i++)
                    {
                        dt.Columns.Add($"Column{i + 1}");
                    }
                }

                for (int i = rowStart; i <= rowCount; i++)
                {
                    row = dt.NewRow();
                    for (int j = colStart; j <= colCount; j++)
                    {
                        var cellVal = worksheet.Cells[i, j].Value;
                        if (i == 1 && hasTitle)
                        {
                            string? temp = cellVal == null ? string.Empty : cellVal.ToString();
                            dt.Columns.Add(temp);
                        }
                        else
                        {
                            string? temp = cellVal == null ? string.Empty : cellVal.ToString();
                            row[j - colStart] = cellVal;
                        }
                    }

                    if (i > 1 || !hasTitle)
                    {
                        dt.Rows.Add(row);
                    }
                }
                package.Dispose();
                result.Data = dt;
                result.State = true;
            }
            catch (Exception ex)
            {
                result.Message = ex.GetFullException();
            }

            return result;
        }



        /// <summary>
        /// 获取工单设置的第二个工作表的信息
        /// </summary>
        /// <param name="productionRecipePathUsed"></param>
        /// <returns></returns>
        public static DataResult<List<RecipeLevelBoxModel>> GetRecipeLevelBoxInfo(string productionRecipePathUsed)
        {
            // 1.这里要提供表头数据,如果表头数据不对,要做提示的
            string[] tableColumnNames = { "Level", "IsEnable", "Type", "TypeDescribe", "MaterialBox" };
            DataResult<List<RecipeLevelBoxModel>> result = new DataResult<List<RecipeLevelBoxModel>>();
            try
            {
                var resultTable = ExcelToDataTableByFilePath(productionRecipePathUsed, sheetIndex: 1);
                if (resultTable.State)
                {
                    var table = resultTable.Data;
                    if (table!.Rows.Count == 0)
                    {
                        result.State = true;
                        return result;
                    }
                    var detectedLevelList = table.AsEnumerable().Select(q => new RecipeLevelBoxModel
                    {
                        Level = int.Parse(q.Field<string>(tableColumnNames[0])!),
                        IsUsed = q.Field<string>(tableColumnNames[1])?.ToUpper() == "TRUE",
                        LevelType = q.Field<string>(tableColumnNames[2]),
                        TypeDescription = q.Field<string>(tableColumnNames[3]),
                        // 去除掉两头的双引号
                        BoxesInto = string.IsNullOrWhiteSpace(q.Field<string>(tableColumnNames[4])) ? string.Empty : q.Field<string>(tableColumnNames[4])!.Trim('\"'),
                    }).OrderBy(q => q.Level).ToList();

                    result.Data = detectedLevelList;
                    result.State = true;
                }
                else
                {
                    result.Message = resultTable.Message;
                }
            }
            catch (Exception ex)
            {
                result.Message = ex.GetFullException();
            }
            return result;
        }
    }
}
